Imports System
Imports System.Text
Imports System.Data
Imports System.Collections.Generic

Imports Framework

#Region "Enum: Primary Key Values"
'<CLSCompliant(True)> _
'Public Enum ECustomer
'    Huey = 1
'    Duey = 2
'    Louie = 3
'End Enum
#End Region

'Table-Row Class (Customisable half)
Partial Public Class CCustomer

#Region "Constants"
    'Join Expressions
    'Private Shared JOIN_SAMPLE As String = String.Concat(TABLE_NAME, " LEFT OUTER JOIN ", CSample.TABLE_NAME, " ON CustomerSampleId=SampleId")
#End Region

#Region "Constructors (Public)"
    'Default Connection String
    Public Sub New()
        MyBase.New()
    End Sub
    Public Sub New([CustomerId] As Integer)
        MyBase.New([CustomerId])
    End Sub

    'Alternative Connection String
    Public Sub New(ByVal dataSrc As CDataSrc)
        MyBase.New(dataSrc)
    End Sub
    Public Sub New(ByVal dataSrc As CDataSrc, [CustomerId] As Integer)
        MyBase.New(dataSrc, [CustomerId])
    End Sub

    'Transactional (shares an open connection)
    Protected Friend Sub New(ByVal dataSrc As CDataSrc, ByVal [CustomerId] As Integer, ByVal txOrNull As IDbTransaction)
        MyBase.New(dataSrc, [CustomerId], txOrNull)
    End Sub
#End Region

#Region "Default Values"
    Protected Overrides Sub InitValues_Custom()
        'Custom default values (e.g. DateCreated column)

        'Member variables (e.g. for child collections)
        m_orders = New COrderList()
    End Sub
#End Region

#Region "Default Connection String"
    Protected Overrides Function DefaultDataSrc() As CDataSrc
        Return CDataSrc.Default
    End Function
#End Region

#Region "Members"
    'Foreign Keys   
    <NonSerialized()> Private m_orders As COrderList

    'Child Collections  

    'Xml Data (as high-level objects)

#End Region

#Region "Properties - Relationships"
    'Relationships - Foriegn Keys (e.g parent)

    'Relationships - Collections (e.g. children)
    Public Property [Orders]() As COrderList
        Get
            If IsNothing(m_orders) Then
                SyncLock (Me)
                    If IsNothing(m_orders) Then
                        m_orders = New COrder(DataSrc).SelectByCustomerId(Me.CustomerId)
                        m_orders.Customer = Me
                    End If
                End SyncLock
            End If
            Return m_orders
        End Get
        Set(Value As COrderList)
            m_orders = Value
            If Not IsNothing(Value) Then
                m_orders.Customer = Me
            End If
        End Set
    End Property
    Friend Function Orders_(ByVal tx As IDbTransaction) As COrderList
        With New COrder(DataSrc)
            Return .SelectByCustomerId(Me.CustomerId, tx)     'Only used for cascade deletes
        End With
    End Function

    'Replaced with a view:
    'Public Function OrdersCount() As Integer
    '    If Not IsNothing(m_orders) Then Return m_orders.Count
    '    With New COrder(DataSrc)
    '        Return .SelectCountByCustomerId(Me.CustomerId)
    '    End With
    'End Function
    'Public Function Sales() As Decimal
    '    With New COrder(DataSrc)
    '        Return .SalesForCustomer(Me.CustomerId)
    '    End With
    'End Function
    'Public Function RemindersCount() As Integer
    '    With New CReminder(DataSrc)
    '        Return .SelectCountByCustomerId(Me.CustomerId)
    '    End With
    'End Function

#End Region

#Region "Properties - Customisation"
    'Derived/ReadOnly (e.g. xml classes, presentation logic)
    Public ReadOnly Property CustomerNameAndEmail As String
        Get
            Return String.Concat(CustomerName, " (", CustomerEmail, ")")
        End Get
    End Property
#End Region

#Region "Save/Delete Overrides"
    Public Overrides Sub Delete(ByVal txOrNull As IDbTransaction)
        'Use a transaction if none supplied
        If txOrNull Is Nothing Then
            BulkDelete(Me)
            Exit Sub
        End If

        'Cascade-Delete (all child collections)
        Me.Orders_(txOrNull).DeleteAll(txOrNull)

        'Normal Delete
        MyBase.Delete(txOrNull)
    End Sub
#End Region

#Region "Custom Database Queries"
    'For Stored Procs can use: MakeListTyped (matching schema), or DataSrc.ExecuteDataset (reports etc)
    'For Dynamic sql, can use: SelectSum, SelectDistinct, SelectCount, SelectWhere (inherited methods)
    '                see also: SelectBy[FK], Search and Count (auto-generated sample queries)
    Public Function SelectByEmail(email As String) As CCustomer
        With SelectWhere("CustomerEmail", ESign.EqualTo, email) 'Has a unique index, so only ever zero or 1 (no paging required)
            If .Count = 0 Then Return Nothing
            Return .Item(0)
        End With
    End Function
#End Region

#Region "Searching (Optional)"
    'Dynamic search methods: (overload as required for common search patterns, cascade the BuildWhere overloads)
    '   Public  x5 - Simple, Paged, Transactional, Count, and Dataset
    '   Private x1 - BuildWhere
    'See also in-memory search options in list class, such as GetBy[FK] and Search

    'Simple
    Public Function SelectSearch(ByVal nameOrId As String, paying As Boolean) As CCustomerList
        Return SelectWhere(BuildWhere(nameOrId, paying)) ', JOIN_OR_VIEW)
    End Function

    'Paged
    Public Function SelectSearch(ByVal pi As CPagingInfo, ByVal nameOrId As String, paying As Boolean) As CCustomerList
        'pi.TableName = JOIN_OR_VIEW
        Return SelectWhere(pi, BuildWhere(nameOrId, paying))
    End Function

    'Transactional
    Public Function SelectSearch(ByVal nameOrId As String, paying As Boolean, ByVal tx As IDbTransaction) As CCustomerList
        Return SelectWhere(BuildWhere(nameOrId, paying), tx) ', JOIN_OR_VIEW, tx)
    End Function

    'Count
    Public Overloads Function SelectCount(ByVal nameOrId As String, paying As Boolean) As Integer
        Return SelectCount(BuildWhere(nameOrId, paying)) ', JOIN_OR_VIEW)
    End Function

    'Dataset (e.g. ExportToCsv)
    Public Function SelectSearch_Dataset(ByVal nameOrId As String, paying As Boolean) As DataSet
        Return SelectWhere_Dataset(BuildWhere(nameOrId, paying)) ', JOIN_OR_VIEW)
    End Function


    'Filter Logic
    'Represents a simple search box to search PK and any string columns
    Private Shared Function BuildWhere(ByVal nameOrId As String, paying As Boolean) As CCriteriaList
        Dim where As New CCriteriaList 'Defaults to AND logic

        If paying Then where.Add("PaymentCount", ESign.GreaterThan, 0)

        'Simple search box UI
        If Not String.IsNullOrEmpty(nameOrId) Then
            'Interpret search string in various ways using OR sub-expression
            Dim orExpr As New CCriteriaGroup(EBoolOperator.Or)

            'Special case - search by PK (assumes integer PK)
            Dim id As Integer
            If Integer.TryParse(nameOrId, id) Then
                orExpr.Add("CustomerId", id)
            End If

            'Search a range of string columns
            Dim wildCards As String = String.Concat("%", nameOrId, "%")
            orExpr.Add("CustomerName", ESign.Like, wildCards)
            orExpr.Add("CustomerEmail", ESign.Like, wildCards)

            'Conclude
            If orExpr.Group.Count > 0 Then
                where.Add(orExpr)
            End If
        End If

        Return where
    End Function  
#End Region

#Region "Cloning"
    Public Function Clone(target As CDataSrc, txOrNull As IDbTransaction) As CCustomer ', parentId As Integer) As CCustomer
        'Shallow copy: Copies the immediate record, excluding autogenerated Pks
        Dim copy As New CCustomer(Me, target)

        'Deep Copy - Child Entities: Cloned children must reference their cloned parent
        'copy.SampleParentId = parentId

        copy.Save(txOrNull)

        'Deep Copy - Parent Entities: Cloned parents also clone their child collections
        'Me.Children.Clone(target, txOrNull, copy.CustomerId)

        Return copy
    End Function
#End Region

#Region "ToXml"
    Protected Overrides Sub ToXml_Custom(ByVal w As System.Xml.XmlWriter)
        'Store(w, "Example", Me.Example)
    End Sub
#End Region


    Public Class CTotals
        Public PaymentCount As Integer
        Public PaymentTotal As Decimal
        Public PaymentNet As Decimal

        Private Shared SELECT_COLS As String = "SUM(PaymentCount), SUM(PaidAmount), SUM(PaidNet)"
        Private Shared JOIN1 As String = "vwMcm_PaymentCount"
        Private Shared JOIN2 As String = "vwMcm_PaymentCount INNER JOIN tblMcm_Orders ON OrderId=PaymentOrderId INNER JOIN tblMcm_Customers ON CustomerId=OrderCustomerId"

        Public Sub New(search As String, paying As Boolean)
            Dim ds As DataSet = Nothing
            If String.IsNullOrEmpty(search) Then
                ds = CDataSrc.Default.SelectAll_Dataset(SELECT_COLS, JOIN1, Nothing)
            Else
                ds = CDataSrc.Default.SelectWhere_Dataset(SELECT_COLS, JOIN2, Nothing, CCustomer.BuildWhere(search, paying))
            End If
            Dim dr As DataRow = ds.Tables(0).Rows(0)

            PaymentCount = CAdoData.GetInt(dr, 0)
            PaymentTotal = CAdoData.GetDec(dr, 1)
            PaymentNet = CAdoData.GetDec(dr, 2)
        End Sub
    End Class

End Class
